package com.excel.operations;

import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;

public class ExcelReader<T> {
	private final static Logger logger = LogManager.getLogger(ExcelReader.class);
	private int skipNoOfRows = 0;
	private final Map<String, Converter> converterMap = new HashMap<String, Converter>();

	public ExcelReader(int skipNoOfRows){
		this.skipNoOfRows = skipNoOfRows;
		try {
			this.init();
		} catch (Exception e) {
			System.out.println("Something is wrong. Review the below stacktrace");
			e.printStackTrace();
		}

	}

	public void registerConverter(String type, Converter objInstance) throws InstantiationException, IllegalAccessException {
		this.converterMap.put(type, objInstance);

	}

	@SuppressWarnings("unchecked")
	private void init() throws InstantiationException, IllegalAccessException, ClassNotFoundException {
		Class<Converter> obj = (Class<Converter>) Class.forName("com.excel.operations.IntegerConverter");
		Converter objInstance = obj.newInstance();
		this.converterMap.put("java.lang.Integer", objInstance);

		obj = (Class<Converter>) Class.forName("com.excel.operations.FloatConverter");
		objInstance = obj.newInstance();
		this.converterMap.put("java.lang.Float", objInstance);

		obj = (Class<Converter>) Class.forName("com.excel.operations.BooleanConverter");
		objInstance = obj.newInstance();
		this.converterMap.put("java.lang.Boolean", objInstance);

		obj = (Class<Converter>) Class.forName("com.excel.operations.DateConverter");
		objInstance = obj.newInstance();
		this.converterMap.put("java.util.Date", objInstance);

		obj = (Class<Converter>) Class.forName("com.excel.operations.StringConverter");
		objInstance = obj.newInstance();
		this.converterMap.put("java.lang.String", objInstance);

		obj = (Class<Converter>) Class.forName("com.excel.operations.DoubleConverter");
		objInstance = obj.newInstance();
		this.converterMap.put("java.lang.Double", objInstance);
	}
	
	public List<T> getObjects(String fileName, int sheetNumber, Class<T> className, Map<String, String> map) {
		try {
			final Workbook wb = ExcelUtils.getWorkBook(fileName);
			Sheet sheet = wb.getSheetAt(sheetNumber-1);
			return getObjects(sheet, className, map);
		} catch (Exception e) {
			System.out.println("Something is wrong. Review the below stacktrace");
			e.printStackTrace();
		}
		return null;
	}

	public List<T> getObjects(Sheet sheet, Class<T> className, Map<String, String> map) throws ClassNotFoundException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException, UnparsbleException {
		final List<T> listObjects = new ArrayList<T>();
		for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
			logger.debug("Processing row" + i);
			if (i < this.skipNoOfRows) continue;
			final T obj = this.createObject(className, sheet.getRow(i), map);
			listObjects.add(obj);
		}
		return listObjects;
	}

	@SuppressWarnings("unchecked")
	private T createObject(Class<T> className, Row row, Map<String, String> map) throws ClassNotFoundException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, InstantiationException, UnparsbleException {

		final Class<T> obj = (Class<T>) Class.forName(className.getName());
		final T objInstance = obj.newInstance();
		logger.debug("Setting properties for class" + className.getName());
		if (row == null) return null;
		for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
			logger.debug("Processing cell" + i);
			
			//Ref by Index
			//final String propertyName = map.get(new Integer(i + 1));
			
			//Ref by Cell Reference
			String colRef = CellReference.convertNumToColString(i);
			//int colIndex = CellReference.convertColStringToIndex("AA");
			final String propertyName = map.get(colRef);
			
			logger.debug("Processing cell propertyName" + propertyName);
			final Cell cell = row.getCell(i);
			if (cell == null) continue;
			if (propertyName == null || propertyName.trim().equalsIgnoreCase("")) {
				continue;
			}
			cell.setCellType(Cell.CELL_TYPE_STRING);
			Object value = null;
			final Object cellValue = this.getCellValue(cell);
			if (cellValue == null) continue;
			logger.debug("Getting descriptor for property propertyName" + propertyName);
			final PropertyDescriptor descriptor = PropertyUtils.getPropertyDescriptor(objInstance, propertyName);
			if (descriptor == null) throw new UnparsbleException("Check if property" + propertyName + "exists");

			final Class<?> type = descriptor.getPropertyType();
			final Converter<?> converter = this.converterMap.get(type.getName());
			if (converter == null) throw new UnparsbleException("No Converters for" + type.getName() + "exists");

			try {
				value = converter.from(cellValue);
			} catch (final Exception e) {
				logger.error(e);
				value = null;
			}
			PropertyUtils.setSimpleProperty(objInstance, propertyName, value);
		}
		return objInstance;
	}

	private Object getCellValue(Cell cell) {
		if (cell == null) return null;
		if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return "";
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) return cell.getNumericCellValue();
		if (cell.getCellType() == Cell.CELL_TYPE_STRING)
		{
			
			return cell.getStringCellValue();
		}
		return null;
	}
}
